package com.sdry.untils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;

/**
 * 导出
 * @author chenBin
 * @Date 2020年7月22日 10:27:57
 */
public class ExcelUtils {
	/**
	 * 导出模板
	 *
	 * @return
	 * @throws UnsupportedEncodingException
	 */
	public static void exportFile(String title, List<String> list,
								  List<List<String>> patents, HttpServletResponse response) throws UnsupportedEncodingException {
		//开始时间
		long startLong = System.currentTimeMillis();
		// 第一步，创建一个webbook，对应一个Excel文件
		//HSSFWorkbook wb = new HSSFWorkbook();

		//设置存储在内存的行数，多余的存储在硬盘
		int cacheItems = 100;
		Workbook wb = new SXSSFWorkbook(cacheItems);

		// 表头 标题样式
		Font titleFont = wb.createFont();
		//字体
		titleFont.setFontName("微软雅黑");
		// 字体大小
		titleFont.setFontHeightInPoints((short) 12);
		CellStyle titleStyle = wb.createCellStyle();
		titleStyle.setFont(titleFont);
		// 左右居中
		titleStyle.setAlignment(HorizontalAlignment.CENTER);
		// 上下居中
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		//下边框
		titleStyle.setBorderBottom(BorderStyle.THIN);
		//左边框
		titleStyle.setBorderLeft(BorderStyle.THIN);
		//上边框
		titleStyle.setBorderTop(BorderStyle.THIN);
		//右边框
		titleStyle.setBorderRight(BorderStyle.THIN);
		//背景颜色
		titleStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
		titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		//设置自动换行
		titleStyle.setWrapText(true);
		titleStyle.setLocked(true);


		//内容样式
		CellStyle cellStyle = wb.createCellStyle();
		// 左右居中
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		// 上下居中
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		//下边框
		cellStyle.setBorderBottom(BorderStyle.THIN);
		//左边框
		cellStyle.setBorderLeft(BorderStyle.THIN);
		//上边框
		cellStyle.setBorderTop(BorderStyle.THIN);
		//右边框
		cellStyle.setBorderRight(BorderStyle.THIN);
		//设置自动换行
		cellStyle.setWrapText(true);
		cellStyle.setLocked(true);

		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		//HSSFSheet sheet = wb.createSheet(title + nowtime);
		Sheet sheet = wb.createSheet();
		if (list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				sheet.setColumnWidth(i, 28 * 256);
			}
		}

		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		Row row = sheet.createRow(0);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		CellStyle style = wb.createCellStyle();
		// 创建一个居中格式
		style.setAlignment(HorizontalAlignment.CENTER);

		row.setHeight((short)265);

		//表头
		if (list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				Cell cell = row.createCell(i);
				cell.setCellValue(list.get(i));
				cell.setCellStyle(titleStyle);
			}
		}

		//内容
		if (patents.size() > 0){
			for (int j = 0; j < patents.size(); j++) {
				Row row1 = sheet.createRow(j+1);
				List<String> list1 = patents.get(j);
				for (int i = 0; i < list1.size(); i++) {
					Cell cell1 = row1.createCell(i);
					cell1.setCellValue(list1.get(i));
					cell1.setCellStyle(cellStyle);
				}
			}
		}

		patents = null;
		list = null;
		download(title, response, wb);
	}

	private static void download(String title, HttpServletResponse response, Workbook wb) throws UnsupportedEncodingException {
		// 第六步，将文件存到指定位置
		String fileName = title+".xlsx";
		fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
		try {
			response.reset();
			response.setHeader("Content-Disposition", "attachment;filename="
					+ fileName);// 指定下载的文件名
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Pragma", "no-cache");
			response.setHeader("Cache-Control", "no-cache");
			response.setDateHeader("Expires", 0);
			OutputStream output = response.getOutputStream();
			BufferedOutputStream bufferedOutPut = new BufferedOutputStream(
					output);
			bufferedOutPut.flush();
			wb.write(bufferedOutPut);
			bufferedOutPut.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	public static void download(String filename, HttpServletResponse res) throws IOException {
		// 发送给客户端的数据
		OutputStream outputStream = res.getOutputStream();
		byte[] buff = new byte[1024];
		BufferedInputStream bis = null;
		// 读取filename
		bis = new BufferedInputStream(new FileInputStream(new File("./file/" + filename)));
		int i = bis.read(buff);
		while (i != -1) {
			outputStream.write(buff, 0, buff.length);
			outputStream.flush();
			i = bis.read(buff);
		}
	}


}
